package com.bsoft.gol.hcs.followup.dao;

import com.google.common.collect.Lists;
import ctd.account.UserRoleToken;
import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import ctd.util.converter.ConversionUtils;
import com.bsoft.gol.hcs.enumcase.FollowUpStatusEnum;
import com.bsoft.gol.hcs.enumcase.FusTabTypeEnum;
import com.bsoft.gol.hcs.followup.bean.FollowUpConst;
import com.bsoft.gol.hcs.followup.bean.request.*;
import com.bsoft.gol.hcs.followup.bean.response.*;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.atag.dao.action.QoHibernateStatelessResultAction;
import org.atag.dao.ext.HibernateExtendDAO;
import org.atag.dao.model.SimplePageInfo;
import org.hibernate.Query;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.StringUtils;
import com.bsoft.gol.hcs.specialist.bean.request.QueryPlanListRequest;
import com.bsoft.gol.hcs.specialist.bean.response.DiseasesAndFusListResponse;
import com.bsoft.gol.hcs.specialist.bean.response.QueryPlanListResponse;
import com.bsoft.gol.hcs.specialist.entity.FusAfterHosRecord;
import com.bsoft.gol.hcs.utils.CommonUtils;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 院后随访DAO
 *
 * @author neoLin - 2022/05/23 13:46
 **/
public abstract class FusAfterHosRecordDAO extends HibernateExtendDAO<FusAfterHosRecord> {
    @Value("${database_type}")
    public String databaseType;//1.mysql  2.达梦    默认mysql

    @DAOMethod(sql = " from FusAfterHosRecord where  notifyFlag = 1 and followUpStatus = 0 and notifyStatus = 0 and " +
            "planNotifyDate=:planNotifyDate")
    public abstract List<FusAfterHosRecord> queryNoticeRecords(@DAOParam("planNotifyDate") Date planNotifyDate);

    @DAOMethod(sql = " from FusAfterHosRecord where notifyFlag = 1 and followUpStatus = 0 and notifyStatus = 1 and " +
            "planNotifyDate=:planNotifyDate")
    public abstract List<FusAfterHosRecord> queryNoticeRecordsYesterday(@DAOParam("planNotifyDate") Date planNotifyDate);

    @DAOMethod(sql = "update FusAfterHosRecord set auditStatus=:auditStatus,auditName=:auditName where recordId in " +
            "(:recordIdList)")
    public abstract void updateAuditStatus(@DAOParam("auditStatus") int auditStatus,
                                           @DAOParam("recordIdList") List<String> recordIdList,
                                           @DAOParam("auditName") String auditName);

    @DAOMethod(sql = "select count(1) from  FusAfterHosRecord where route_id = :routeId and follow_up_status = 0")
    public abstract Long getRecordsCount(@DAOParam("routeId") Long routeId);


    @DAOMethod(sql = " from  FusAfterHosRecord where cardType =:cardType and cardNumber=:cardNumber")
    public abstract List<FusAfterHosRecord> queryRecordsByPerson(@DAOParam("cardType") String cardType, @DAOParam("cardNumber") String cardNumber,
                                                                 @DAOParam(pageStart = true) int start, @DAOParam(pageLimit = true) int limit);


    @DAOMethod(sql = " from  FusAfterHosRecord where cardType =:cardType and cardNumber=:cardNumber and routeDetailType=6")
    public abstract List<FusAfterHosRecord> queryRecordsEducationByPerson(@DAOParam("cardType") String cardType, @DAOParam("cardNumber") String cardNumber,
                                                                 @DAOParam(pageStart = true) int start, @DAOParam(pageLimit = true) int limit);


    /**
     * 根据病人查询所有未执行,未锁定的随访计划
     */
    @DAOMethod(sql = " FROM FusAfterHosRecord WHERE patientId=:patientId AND followUpStatus=0")
    public abstract List<FusAfterHosRecord> findAllReadyPlanByPatientId(@DAOParam("patientId") String patientId);

    @DAOMethod(sql = " update FusAfterHosRecord set followUpStatus=:followUpStatus  where recordId =:recordId ")
    public abstract void updateFollowUpStastusById(@DAOParam("followUpStatus") Byte followUpStatus, @DAOParam("recordId") String recordId);

    /**
     * 随访计划加锁
     */
    public void lockRecord(String recordId, String lockId, String lockName) {
        HibernateStatelessResultAction<Integer> action = new AbstractHibernateStatelessResultAction<Integer>() {
            @Override
            public void execute(StatelessSession statelessSession) {
                String sql = "UPDATE fus_after_hos_record set lock_id=:lockId, lock_name=:lockName,lock_flag=1, lock_time=NOW()" +
                        "  WHERE record_id=:recordId AND lock_flag=0";
                Query query = statelessSession.createSQLQuery(sql);
                query.setParameter("lockId", lockId);
                query.setParameter("recordId", recordId);
                query.setParameter("lockName", lockName);
                query.executeUpdate();
            }
        };
        HibernateSessionTemplate.instance().execute(action);
    }

    /**
     * 随访计划解锁,只有自己或者管理员能够解锁,当lockId不传时为管理员解锁
     */
    public void unlockRecord(String recordId, String lockId) {
        HibernateStatelessResultAction<Integer> action = new AbstractHibernateStatelessResultAction<Integer>() {
            @Override
            public void execute(StatelessSession statelessSession) {
                String sql = "UPDATE fus_after_hos_record set lock_id=NULL, lock_name=NULL,lock_flag=0" +
                        "  WHERE record_id=:recordId AND lock_flag=1";
                if (!StringUtils.isEmpty(lockId)) {
                    sql = sql + " AND lock_id=:lockId ";
                }
                Query query = statelessSession.createSQLQuery(sql);
                if (!StringUtils.isEmpty(lockId)) {
                    query.setParameter("lockId", lockId);
                }

                query.setParameter("recordId", recordId);
                query.executeUpdate();
            }
        };
        HibernateSessionTemplate.instance().execute(action);
    }

    /**
     * 随访计划解锁,只有自己或者管理员能够解锁,当lockId不传时为管理员解锁
     */
    public void updateFollowUpRecordsResult(String recordId, Byte resultCode,Byte followUpStatus,String executeId,String executeName) {
        HibernateStatelessResultAction<Void> action = new AbstractHibernateStatelessResultAction<Void>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" update fus_after_hos_record set result_code=:resultCode,follow_up_status=:followUpStatus,execute_id=:executeId" +
                        ",execute_name=:executeName,execute_time=:executeTime,modify_dt=:gmtModified where record_id=:recordId ");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("recordId", recordId);
                query.setParameter("resultCode", resultCode);
                query.setParameter("followUpStatus", followUpStatus);
                query.setParameter("executeId", executeId);
                query.setParameter("executeName", executeName);
                query.setParameter("executeTime", new Date());
                query.setParameter("gmtModified", new Date());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                query.executeUpdate();
            }
        };
        HibernateSessionTemplate.instance().execute(action);
    }


    /**
     * 查询上锁是否成功
     */
    public boolean lockSuccess(String recordId, String lockId) {
        FusAfterHosRecord query = new FusAfterHosRecord();
        query.setRecordId(recordId);
        query.setLockId(lockId);
        query.setLockFlag(true);
        FusAfterHosRecord result = this.selectOne(query);
        return Objects.nonNull(result);
    }

    /**
     * 查询随访是否非锁定状态
     */
    public boolean getUnLockState(String recordId) {
        FusAfterHosRecord query = new FusAfterHosRecord();
        query.setRecordId(recordId);
        query.setLockFlag(false);
        FusAfterHosRecord result = this.selectOne(query);
        return Objects.nonNull(result);
    }

    /**
     * 获取随访记录分页对象
     */
    public SimplePageInfo<FollowUpRecordListVO> recordPage(FollowUpRecordListDTO dto) {
        //分页查询控件里面有排除了空字符串的情况，所以前端那边传了空字符串，这边把机构代码设为null,使查询结果返回空
        if("".equals(dto.getOrganizationId())){
            dto.setOrganizationId("null");
        }
        dto.setTenantId(UserRoleToken.getCurrentTenantId());
        QoHibernateStatelessResultAction<FollowUpRecordListVO, FollowUpRecordListDTO> action = getRecordListAction(dto, true);
        HibernateSessionTemplate.instance().execute(action);
        return action.getSimpleResultPage();
    }

    /**
     * 查询随访记录列表
     */
    public List<SimpleFollowUpRecordListVO> queryRecordList(SimpleFollowUpRecordListDTO dto) {

        QoHibernateStatelessResultAction<SimpleFollowUpRecordListVO, SimpleFollowUpRecordListDTO> action = new QoHibernateStatelessResultAction<>(false, dto, SimpleFollowUpRecordListVO.class);

        String sql = "SELECT" +
                " {{" +
                " a.record_id AS recordId, a.patient_name AS patientName, a.patient_sex AS patientSex, " +
                " a.patient_age AS patientAge, a.plan_time AS planTime, a.department_name AS departmentName, " +
                " a.patient_type AS patientType " +
                "}} " +
                " FROM " +
                " fus_after_hos_record a " +
                " WHERE " +
                " (a.audit_status = 0 OR a.audit_status =2) ";

        action.setSql(sql);
        action.addCondition(" AND a.tenant_id = :tenantId ");
        action.addCondition(" AND a.patient_type = :followUpType ");
        action.addCondition(" AND a.organization_id = :organizationId ");
        action.addCondition(" AND a.department_id = :departmentId ");
        action.addCondition(" AND a.follow_up_status = :followUpStatus ");
        Integer type = dto.getType();
        if (Objects.nonNull(type) && type == 1) {
            action.addSql(" AND a.hang_up_flag = 1 ");
        }

        Integer tabType = dto.getTabType();
        List<Byte> followUpStatusList = new ArrayList();
        dto.setFollowUpStatusList(followUpStatusList);
        if (Objects.nonNull(tabType)) {
            setConditionByTabType(tabType, action, followUpStatusList);
        }
        //随访类型（0门诊1住院）
        Integer followUpType = dto.getFollowUpType();
        String keyWord = dto.getKeyWord();
        setSearchValue(action, followUpType, keyWord);

        action.addSql(" ORDER BY a.plan_time DESC,record_id DESC ");
        HibernateSessionTemplate.instance().execute(action);
        return action.getResultList();
    }

    /**
     * 随访记录列表, 导出用,不分页
     */
    public List<FollowUpRecordListVO> recordListForExcel(FollowUpRecordListDTO dto) {
        dto.setTenantId(UserRoleToken.getCurrentTenantId());
        QoHibernateStatelessResultAction<FollowUpRecordListVO, FollowUpRecordListDTO> action = getRecordListAction(dto, false);
        HibernateSessionTemplate.instance().execute(action);
        return action.getResultList();
    }


    /**
     * 获取随访记录统计值
     */
    public Long getCount(FollowUpRecordListDTO dto) {
        dto.setPageNo(1);
        dto.setPageSize(0);
        dto.setTenantId(UserRoleToken.getCurrentTenantId());
        QoHibernateStatelessResultAction<FollowUpRecordListVO, FollowUpRecordListDTO> action = getCountAction(dto, true);
        HibernateSessionTemplate.instance().execute(action);
        return action.getSimpleResultPage().getTotal();
    }

    /**
     * 根据科室聚合随访数量统计值
     */
    public List<FollowUpUnitCountVO> getFollowUpDeptCount(FollowUpRecordListDTO dto) {

        String sql = "SELECT department_id AS code, count(department_id) AS totalCount FROM fus_after_hos_record a " +
                "WHERE (a.audit_status = 0 OR a.audit_status =2) ";
        QoHibernateStatelessResultAction<FollowUpUnitCountVO, FollowUpRecordListDTO> action =
                new QoHibernateStatelessResultAction<>(false, dto, FollowUpUnitCountVO.class);

        action.setSql(sql);
        action.addCondition(" AND a.tenant_id = :tenantId ");
        action.addCondition(" AND a.patient_type = :followUpType ");
        Integer tabType = dto.getTabType();
        List<Byte> followUpStatusList = new ArrayList();
        dto.setFollowUpStatusList(followUpStatusList);
        if (Objects.nonNull(tabType)) {
            setConditionByTabType(tabType, action, followUpStatusList);
        }
        action.addSql(" GROUP BY department_id");
        HibernateSessionTemplate.instance().execute(action);
        return action.getResultList();
    }

    /**
     * 根据机构聚合随访数量统计值
     */
    public List<FollowUpUnitCountVO> getFollowUpOrgCount(FollowUpRecordListDTO dto) {

        String sql = "SELECT organization_id AS code, count(organization_id) AS totalCount FROM fus_after_hos_record " +
                "a WHERE (a.audit_status = 0 OR a.audit_status =2) ";
        QoHibernateStatelessResultAction<FollowUpUnitCountVO, FollowUpRecordListDTO> action =
                new QoHibernateStatelessResultAction<>(false, dto, FollowUpUnitCountVO.class);
        action.setSql(sql);
        action.addCondition(" AND a.tenant_id = :tenantId ");
        action.addCondition(" AND a.patient_type = :followUpType ");
        Integer tabType = dto.getTabType();
        List<Byte> followUpStatusList = new ArrayList();
        dto.setFollowUpStatusList(followUpStatusList);
        if (Objects.nonNull(tabType)) {
            setConditionByTabType(tabType, action, followUpStatusList);
        }
        action.addSql(" GROUP BY organization_id");

        HibernateSessionTemplate.instance().execute(action);
        return action.getResultList();
    }

    private String getCountSql() {
        return "SELECT" +
                " {{" +
                " 1 " +
                "}} " +
                " FROM " +
                " fus_after_hos_record a " +
                " WHERE " +
                " (a.audit_status = 0 OR a.audit_status =2) ";
    }


    private QoHibernateStatelessResultAction<FollowUpRecordListVO, FollowUpRecordListDTO> getCountAction(FollowUpRecordListDTO dto, boolean isPage) {

        QoHibernateStatelessResultAction<FollowUpRecordListVO, FollowUpRecordListDTO> action =
                new QoHibernateStatelessResultAction<>(isPage, dto, FollowUpRecordListVO.class);
        String sql = getCountSql();
        action.setSql(sql);
        action.addCondition(" AND a.tenant_id = :tenantId ");
        action.addCondition(" AND a.patient_type = :followUpType ");
        Integer tabType = dto.getTabType();
        List<Byte> followUpStatusList = new ArrayList();
        dto.setFollowUpStatusList(followUpStatusList);
        if (Objects.nonNull(tabType)) {
            setConditionByTabType(tabType, action, followUpStatusList);
        }

        return action;
    }


    private QoHibernateStatelessResultAction<FollowUpRecordListVO, FollowUpRecordListDTO> getRecordListAction(FollowUpRecordListDTO dto, boolean isPage) {
        QoHibernateStatelessResultAction<FollowUpRecordListVO, FollowUpRecordListDTO> action = new QoHibernateStatelessResultAction<>(isPage, dto, FollowUpRecordListVO.class);
        Integer followUpType = dto.getFollowUpType();
        String selectColumn = "";
        String joinSql = "";
        if (FollowUpConst.FOLLOW_UP_TYPE_OUT.equals(followUpType)) {
            //门诊
            selectColumn = ", b.main_diagnosis_name AS disDiagnosisName ";
            joinSql = " LEFT JOIN vr_outpatient_visit_record b ON a.visit_no=b.visit_no";
        } else if (FollowUpConst.FOLLOW_UP_TYPE_IN.equals(followUpType)) {
            //住院
            selectColumn = ", b.discharge_diagnosis AS disDiagnosisName ";
            joinSql = " LEFT JOIN vr_inpatient_visit_record b ON a.visit_no=b.visit_no";
        }
        String sql = "SELECT" +
                " {{" +
                "a.record_id AS recordId,a.tenant_id AS tenantId,a.organization_id AS organizationId,a.organization_name AS organizationName," +
                "a.route_detail_type AS routeDetailType,a.department_id AS departmentId,a.department_name AS departmentName,a.doctor_id AS doctorId," +
                "a.doc_id AS docId,a.doctor_name AS doctorName,a.patient_id AS patientId,a.card_type AS cardType,a.card_number AS cardNumber," +
                "a.patient_name AS patientName,a.patient_sex AS patientSex,a.patient_age AS patientAge,a.telephone AS telephone,a.address AS address," +
                "a.plan_time AS planTime,a.result_code AS resultCode,a.result_code_detail AS resultCodeDetail,a.follow_up_status AS followUpStatus," +
                "a.create_id AS createId,a.create_time AS createTime,a.execute_id AS executeId,a.execute_name AS executeName,a.execute_time AS executeTime," +
                "a.stop_id AS stopId,a.stop_time AS stopTime,a.lock_id AS lockId,a.lock_name AS lockName,a.lock_time AS lockTime,a.lock_flag AS lockFlag," +
                "a.route_id AS routeId,a.route_name AS routeName,a.route_detail_id AS routeDetailId,a.read_flag AS readFlag,a.notify_flag AS notifyFlag," +
                "a.plan_notify_date AS planNotifyDate,a.notify_status AS notifyStatus,a.medical_record_id AS medicalRecordId,a.medical_record_name AS medicalRecordName," +
                "a.patient_medical_card_number AS patientMedicalCardNumber,a.patient_medical_card_type AS patientMedicalCardType,a.modify_dt AS modifyDt," +
                "a.patient_type AS patientType,a.outpatient_number AS outpatientNumber,a.inpatient_number AS inpatientNumber,a.cancel_id AS cancelId,a.cancel_name AS cancelName," +
                "a.cancel_time AS cancelTime,a.cancel_reason AS cancelReason,a.cancel_type AS cancelType,a.visit_no AS visitNo " +
                selectColumn +
                "}} " +
                " FROM " +
                " fus_after_hos_record a " +
                joinSql +
                " WHERE " +
                " (a.audit_status = 0 OR a.audit_status =2) ";
        action.setSql(sql);
        action.addCondition(" AND a.tenant_id = :tenantId ");
        action.addCondition(" AND a.patient_type = :followUpType ");
        action.addCondition(" AND a.plan_time >= :planStartTime ");
        action.addCondition(" AND a.plan_time <= :planEndTime ");
        action.addCondition(" AND a.organization_id = :organizationId ");
//        action.addCondition(" AND a.department_id = :departmentId ");
        action.addCondition(" AND a.department_id in (:departmentIds) ");
        if (dto.isShowManual()) {
            action.addSql(" AND plan_produce_type = '1' ");
        } else {
            action.addCondition(" AND a.route_id = :routeId ");
            //routeId 为空则查询全部
            if (Objects.nonNull(dto.getRouteId())) {
                action.addSql(" AND plan_produce_type = '2' ");
            }

        }
        Integer tabType = dto.getTabType();
        List<Byte> followUpStatusList = new ArrayList();
        dto.setFollowUpStatusList(followUpStatusList);
        if (Objects.nonNull(tabType)) {
            setConditionByTabType(tabType, action, followUpStatusList);
        }
        //随访类型（1门诊2住院）
        String keyWord = dto.getKeyWord();
        setSearchValue(action, followUpType, keyWord);
        action.addSql(" ORDER BY a.plan_time DESC, record_id DESC ");
        return action;
    }


    private void setSearchValue(QoHibernateStatelessResultAction<?, ?> action, Integer followUpType, String keyWord) {
        if (!StringUtils.isEmpty(keyWord)) {
            if (isNumber(keyWord)) {
                if (Integer.valueOf(1).equals(followUpType)) {
                    //门诊号码
                    action.addCondition(" AND a.outpatient_number like :keyWord","keyWord",keyWord+"%");
                } else if (Integer.valueOf(2).equals(followUpType)) {
                    //住院号码
                    action.addCondition(" AND a.inpatient_number like :keyWord","keyWord",keyWord+"%");
                }
            } else {
                //姓名
                action.addCondition(" AND a.patient_name like :keyWord","keyWord","%"+keyWord+"%");
            }
        }
    }


    private boolean isNumber(String number) {
        if (StringUtils.isEmpty(number)) {
            return false;
        }
        Pattern p = Pattern.compile("[0-9]+");
        Matcher m = p.matcher(number);
        return m.find();
    }


    /**
     * 1.今日任务2.待执行3.已执行4.已取消5.我的工作
     * ① 今日任务：展示当前路径下所有今日及之前待执行的随访任务数量，不包含已锁定到单个随访人员的任务
     * ② 待执行：展示所有待执行随访任务，包含已锁定到单个随访人员的任务，非管理员无法取消锁定别人的任务
     * ③ 已执行：展示所有已执行随访任务
     * ④ 已取消：展示已取消的随访
     * ⑤ 我的工作：展示所有锁定到当前登录用户个人的随访任务，数字提示待执行数量,可取消锁定
     * 院后随访的今日任务、待执行、我的工作都要展示待确认状态的记录--by 童图平 来源2023-02-06定世都需求
     */
    private void setConditionByTabType(Integer tabType, QoHibernateStatelessResultAction<?, ?> action,
                                       List<Byte> followUpStatusList) {

        FusTabTypeEnum tabTypeEnum = FusTabTypeEnum.getByType(tabType);
        if (Objects.isNull(tabTypeEnum)) {
            return;
        }
        action.addCondition(" AND a.follow_up_status in (:followUpStatusList) ");
        switch (tabTypeEnum) {
            case TODAY:
                followUpStatusList.add(FollowUpStatusEnum.READY.getStatus());
                followUpStatusList.add(FollowUpStatusEnum.UN_CONFIRM.getStatus());
//                action.addSql(" AND a.follow_up_status = " + FollowUpStatusEnum.READY.getStatus() + " ");
                action.addSql(" AND a.lock_flag = 0 ");
                action.addSql(" AND a.plan_time <= '" + DateFormatUtils.format(new Date(), "yyyy-MM-dd") + "' ");
                break;
            case TODO:
                followUpStatusList.add(FollowUpStatusEnum.READY.getStatus());
                followUpStatusList.add(FollowUpStatusEnum.UN_CONFIRM.getStatus());
//                action.addSql(" AND a.follow_up_status = " + FollowUpStatusEnum.READY.getStatus() + " ");
                break;
            case FINISH:
                followUpStatusList.add(FollowUpStatusEnum.FINISH.getStatus());
//                action.addSql(" AND a.follow_up_status = " + FollowUpStatusEnum.FINISH.getStatus() + " ");
                break;
            case CANCELED:
                followUpStatusList.add(FollowUpStatusEnum.CANCEL.getStatus());
//                action.addSql(" AND a.follow_up_status = " + FollowUpStatusEnum.CANCEL.getStatus() + " ");
                break;
            case MY_JOB:
                followUpStatusList.add(FollowUpStatusEnum.READY.getStatus());
                followUpStatusList.add(FollowUpStatusEnum.UN_CONFIRM.getStatus());
//                action.addSql(" AND a.follow_up_status = " + FollowUpStatusEnum.READY.getStatus() + " ");
                action.addCondition(" AND a.lock_id = :docId");
                break;
            default:
                break;
        }
    }

    /**
     * 随访计划中查询住院病人列表
     */
    public SimplePageInfo<FusInpatientListVO> queryInpatientList(FusPatientListDTO dto) {

        QoHibernateStatelessResultAction<FusInpatientListVO, FusPatientListDTO> action = new QoHibernateStatelessResultAction<>(true, dto, FusInpatientListVO.class);
        String sql = "SELECT {{T.*}} FROM (SELECT " +
                " a.patient_id AS patientId,a.patient_name AS patientName, a.in_hospital_record_code AS patientNumber, a.doctor_name AS doctorName," +
                " a.bed_number AS bedNumber, a.discharge_diagnosis AS diagnosisName, a.discharge_time AS dischargeTime,a.organization_id AS organizationId, " +
                " a.visit_no AS visitNo " +
                " FROM " +
                " vr_inpatient_visit_record a " +
                " INNER JOIN vr_visit_diagnosis b ON a.hospital_record_id = b.business_id  " +
                " AND b.business_type = '2'  " +
                " LEFT JOIN follow_patient_label c ON a.visit_no = c.visit_no AND c.source=1 AND a.organization_id=c.organization_id  " +
                "WHERE ";

        action.setSql(sql);
        Integer status = dto.getStatus();
        if (Objects.isNull(status)) {
            action.addSql(" 1=1 ");
        } else if (status == 0) {
            action.addSql(" (a.fus_after_plan_times IS NULL OR a.fus_after_plan_times=0) ");
        } else if (status == 1) {
            action.addSql(" a.fus_after_plan_times > 0 ");
        } else {
            action.addSql(" 1=1 ");
        }
        action.addCondition(" AND a.organization_id = :organizationId ");
        action.addCondition(" AND a.department_id = :departmentId ");
        action.addCondition(" AND a.discharge_time >= :startTime ");
        action.addCondition(" AND a.discharge_time <= :endTime ");

        //患者标签查询条件
        action.addCondition(" AND c.dispute_type in (:patientTags) ");
        //诊断查询条件
        action.addCondition(" AND b.diseases_code in (:diagnoses) ");

        String keyWord = dto.getKeyWord();
        if (!StringUtils.isEmpty(keyWord)) {
            if (isNumber(keyWord)) {
                //门诊号码
                action.addCondition(" AND a.in_hospital_record_code = :keyWord");
            } else {
                //姓名
                action.addCondition(" AND a.patient_name = :keyWord");
            }
        }
        action.addSql("GROUP BY a.visit_no) T  ");
        HibernateSessionTemplate.instance().execute(action);
        return action.getSimpleResultPage();
    }

    /**
     * 随访计划中查询门诊病人列表
     */
    public SimplePageInfo<FusOutpatientListVO> queryOutpatientList(FusPatientListDTO dto) {

        QoHibernateStatelessResultAction<FusOutpatientListVO, FusPatientListDTO> action = new QoHibernateStatelessResultAction<>(true, dto, FusOutpatientListVO.class);
        String sql = "SELECT {{T.*}} FROM (SELECT " +
                " a.patient_id AS patientId,a.patient_name AS patientName, a.patient_medical_card_number AS patientNumber, a.doctor_name AS doctorName," +
                " a.main_diagnosis_name AS diagnosisName, a.start_time AS visitTime,a.organization_id AS organizationId,  " +
                " a.visit_no AS visitNo " +
                " FROM " +
                " vr_outpatient_visit_record a " +
                " INNER JOIN vr_visit_diagnosis b ON a.visit_record_id = b.business_id  " +
                " LEFT JOIN follow_patient_label c ON a.visit_no = c.visit_no AND c.source=2 AND  a.organization_id=c.organization_id " +
                " AND b.business_type = '1'  " +
                "WHERE ";

        action.setSql(sql);
        Integer status = dto.getStatus();
        if (Objects.isNull(status)) {
            action.addSql(" 1=1 ");
        } else if (status == 0) {
            action.addSql(" (a.fus_after_plan_times IS NULL OR a.fus_after_plan_times=0) ");
        } else if (status == 1) {
            action.addSql(" a.fus_after_plan_times > 0 ");
        } else {
            action.addSql(" 1=1 ");
        }
        action.addCondition(" AND a.organization_id = :organizationId ");
        action.addCondition(" AND a.department_id = :departmentId ");
        action.addCondition(" AND a.start_time >= :startTime ");
        action.addCondition(" AND a.start_time <= :endTime ");

        //患者标签查询条件
        action.addCondition(" AND c.dispute_type in (:patientTags) ");
        //诊断查询条件
        action.addCondition(" AND b.diseases_code in (:diagnoses) ");

        String keyWord = dto.getKeyWord();
        if (!StringUtils.isEmpty(keyWord)) {
            if (isNumber(keyWord)) {
                //门诊号码
                action.addCondition(" AND a.visit_no = :keyWord");
            } else {
                //姓名
                action.addCondition(" AND a.patient_name = :keyWord");
            }
        }
        action.addSql("GROUP BY a.visit_no) T  ");
        HibernateSessionTemplate.instance().execute(action);
        return action.getSimpleResultPage();
    }

    public List<FusAfterHosRecord> querySetSmsList(String today) {
        HibernateStatelessResultAction<List<FusAfterHosRecord>> action =
                new AbstractHibernateStatelessResultAction<List<FusAfterHosRecord>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append("SELECT DISTINCT a.record_id recordId,a.visit_time visitTime,card_type cardType,card_number cardNumber,");
                        sqlBuilder.append("patient_id patientId,tenant_id tenantId, organization_name organizationName, patient_name patientName FROM fus_after_hos_record a ");
                        sqlBuilder.append("INNER JOIN fus_after_health_education b ON a.record_id = b.record_id WHERE plan_time = :today");
                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        query.setParameter("today", today);
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                        List<Map> list = query.list();
                        List<FusAfterHosRecord> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, FusAfterHosRecord.class));
                            }
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    /**
     * 随访人员工作量统计
     */
    public SimplePageInfo<FollowUpSatDocVO> doctorFusStatistics(FollowUpSatDocDTO dto) {
        //分页查询控件里面有排除了空字符串的情况，所以前端那边传了空字符串，这边把机构代码设为null,使查询结果返回空
        if("".equals(dto.getOrganizationId())){
            dto.setOrganizationId("null");
        }
        QoHibernateStatelessResultAction<FollowUpSatDocVO, FollowUpSatDocDTO> action = getDoctorFusStatisticsAction(dto, true);
        HibernateSessionTemplate.instance().execute(action);
        return action.getSimpleResultPage();
    }

    /**
     * 随访人员工作量统计导出
     */
    public List<FollowUpSatDocVO> doctorFusStatisticsExport(FollowUpSatDocDTO dto) {
        QoHibernateStatelessResultAction<FollowUpSatDocVO, FollowUpSatDocDTO> action = getDoctorFusStatisticsAction(dto, false);
        HibernateSessionTemplate.instance().execute(action);
        return action.getResultList();
    }


    /**
     * 人员随访统计基础action
     */
    private QoHibernateStatelessResultAction<FollowUpSatDocVO, FollowUpSatDocDTO> getDoctorFusStatisticsAction(FollowUpSatDocDTO dto, boolean isPage) {
        QoHibernateStatelessResultAction<FollowUpSatDocVO, FollowUpSatDocDTO> action = new QoHibernateStatelessResultAction<>(isPage, dto, FollowUpSatDocVO.class);
        String sql = getBasePersonSatSql();
        action.setSql(sql);
        action.addCondition(" AND execute_name = :doctorName ");
        action.addCondition(" AND execute_id = :doctorId ");
        action.addCondition(" AND organization_id = :organizationId ");
        action.addCondition(" AND plan_time >= :beginDate ");
        action.addCondition(" AND plan_time <= :endDate ");
        action.addSql(" GROUP BY organization_id,execute_id,organization_name,execute_name ");
        action.addSql(" ) AS A");
        return action;
    }

    /**
     * 获取指定查询条件下的总的统计数据
     */
    public FollowUpTotalSatVO findAllFusCount(FollowUpSatDocDTO dto) {
        HibernateStatelessResultAction<FollowUpTotalSatVO> action =
                new AbstractHibernateStatelessResultAction<FollowUpTotalSatVO>() {
                    @Override
                    public void execute(StatelessSession statelessSession) throws Exception {
                        String sql = "SELECT COUNT( 1 ) AS followUpVisits, " +
                                " COUNT( CASE WHEN first_flag = 1  THEN 1 END ) AS firstFollowUp, " +
                                " COUNT( CASE WHEN first_flag = 0 THEN 1 END ) AS secondFollowUp " +
                                " FROM fus_after_hos_record where follow_up_status=1 ";
                        StringBuilder sb = new StringBuilder(sql);
                        if (null != dto.getOrganizationId()) {
                            sb.append(" and organization_id =:organizationId ");
                        }
                        if (CommonUtils.isNotEmpty(dto.getDoctorId())) {
                            sb.append(" and doctor_id =:doctorId ");
                        }
                        if (CommonUtils.isNotEmpty(dto.getDoctorName())) {
                            sb.append(" and doctor_name = :doctorName ");
                        }
                        if (CommonUtils.isNotEmpty(dto.getBeginDate())) {
                            sb.append(" and plan_time >= :beginDate ");
                        }
                        if (CommonUtils.isNotEmpty(dto.getEndDate())) {
                            sb.append(" and plan_time <= :endDate ");
                        }
                        Query query = statelessSession.createSQLQuery(sb.toString());
                        if (null != dto.getOrganizationId()) {
                            query.setParameter("organizationId", dto.getOrganizationId());
                        }
                        if (CommonUtils.isNotEmpty(dto.getDoctorId())) {
                            query.setParameter("doctorId", dto.getDoctorId());
                        }
                        if (CommonUtils.isNotEmpty(dto.getDoctorName())) {
                            query.setParameter("doctorName", dto.getDoctorName());
                        }
                        if (CommonUtils.isNotEmpty(dto.getBeginDate())) {
                            query.setParameter("beginDate", dto.getBeginDate());
                        }
                        if (CommonUtils.isNotEmpty(dto.getEndDate())) {
                            query.setParameter("endDate", dto.getEndDate());
                        }
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        Map result = (Map) query.uniqueResult();
                        FollowUpTotalSatVO convert = ConversionUtils.convert(result, FollowUpTotalSatVO.class);
                        setResult(convert);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();

    }

    /**
     * 获得基础统计sql  人员
     */
    private String getBasePersonSatSql() {
        String sql = "SELECT {{A.*}} FROM (SELECT organization_id AS organizationId, organization_name AS organizationName, " +
                " execute_id AS doctorId, execute_name AS doctorName, " +
                " COUNT( 1 ) AS followUpVisits, " +
                " COUNT( CASE WHEN first_flag = 1  THEN 1 END ) AS firstFollowUp, " +
                " COUNT( CASE WHEN first_flag = 0 THEN 1 END ) AS secondFollowUp  " +
                " FROM fus_after_hos_record  WHERE follow_up_status=1 ";
        return sql;
    }


    /**
     * 科室,路径 随访工作量统计
     */
    public SimplePageInfo<FollowUpSatVO> getFusStatistics(FollowUpSatDTO dto, boolean groupByDept) {
        //分页查询控件里面有排除了空字符串的情况，所以前端那边传了空字符串，这边把机构代码设为null,使查询结果返回空
        if("".equals(dto.getOrganizationId())){
            dto.setOrganizationId("null");
        }
        QoHibernateStatelessResultAction<FollowUpSatVO, FollowUpSatDTO> action = getFusStatisticsAction(dto, true, groupByDept);
        HibernateSessionTemplate.instance().execute(action);
        return action.getSimpleResultPage();
    }


    /**
     * 科室,路径 随访工作量统计导出列表
     */
    public List<FollowUpSatVO> getFusStatisticsExport(FollowUpSatDTO dto, boolean groupByDept) {
        QoHibernateStatelessResultAction<FollowUpSatVO, FollowUpSatDTO> action = getFusStatisticsAction(dto, false, groupByDept);
        HibernateSessionTemplate.instance().execute(action);
        return action.getResultList();
    }

    /**
     * 随访统计科室.路径基础action
     */
    private QoHibernateStatelessResultAction<FollowUpSatVO, FollowUpSatDTO> getFusStatisticsAction(FollowUpSatDTO dto, boolean isPage, boolean groupByDept) {
        QoHibernateStatelessResultAction<FollowUpSatVO, FollowUpSatDTO> action = new QoHibernateStatelessResultAction<>(isPage, dto, FollowUpSatVO.class);
        String sql = getBaseSatSql(groupByDept);
        action.setSql(sql);
        action.addCondition(" AND route_id = :routeId ");
        action.addCondition(" AND department_id = :departmentId ");
        action.addCondition(" AND organization_id = :organizationId ");
        action.addCondition(" AND plan_time >= :beginDate ");
        action.addCondition(" AND plan_time <= :endDate ");
        if (groupByDept) {
            action.addSql(" GROUP BY organization_id,department_id,organization_name,department_name");
        } else {
            action.addSql(" GROUP BY route_id,organization_id,route_name,organization_name");
        }
        action.addSql(" ) AS A");
        return action;
    }

    /**
     * 获得基础统计sql  科室 路径
     */
    private String getBaseSatSql(boolean groupByDept) {
        String s2="route_id AS routeId, route_name AS routeName,";
        //科室
        if (groupByDept) {
           s2="department_id AS departmentId, department_name AS departmentName, ";
        }
        String sql = "SELECT {{A.*}} FROM (SELECT organization_id AS organizationId, organization_name AS organizationName, " +s2+
                " COUNT( 1 ) AS followUpVisits, " +
                " COUNT( CASE WHEN (plan_time =execute_date AND follow_up_status=1 ) THEN 1 END ) AS finishVisits, " +
                " COUNT( CASE WHEN follow_up_status = 3 THEN 1 END ) AS cancelVisits, " +
                " COUNT( CASE WHEN (first_flag = 1 AND follow_up_status=1) THEN 1 END ) AS firstFollowUp, " +
                " COUNT( CASE WHEN (first_flag = 0 AND follow_up_status=1) THEN 1 END ) AS secondFollowUp  " +
                " FROM fus_after_hos_record  WHERE 1 = 1 ";
        return sql;
    }

    /**
     * 判断当前患者是初次随访还是再次随访
     */
    public boolean isFirstFollowUp(String patientId, String organizationId, String tenantId) {
        HibernateStatelessResultAction<Long> action = new AbstractHibernateStatelessResultAction<Long>() {
            @Override
            public void execute(StatelessSession statelessSession) {
                String tenantIdClause = "";
                if (!StringUtils.isEmpty(tenantId)) {
                    tenantIdClause = " AND tenant_id=:tenantId ";
                }
                String sql = "SELECT COUNT(1) FROM fus_after_hos_record WHERE patient_id=:patientId AND organization_id=:organizationId " + tenantIdClause + " limit 1";
                Query query = statelessSession.createSQLQuery(sql);
                query.setParameter("patientId", patientId);
                query.setParameter("organizationId", organizationId);
                if (!StringUtils.isEmpty(tenantId)) {
                    query.setParameter("tenantId", tenantId);
                }

                Object result = query.uniqueResult();
                Long count = ConversionUtils.convert(result, Long.class);
                setResult(count);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        Long result = action.getResult();
        return result == 0;
    }

    //居民端查询随访的计划时间 列表
    public List<FusAfterHosRecord> queryDateGroupList(final String cardType, final String cardNumber, final String today) {
        HibernateStatelessResultAction<List<FusAfterHosRecord>> action = new AbstractHibernateStatelessResultAction<List<FusAfterHosRecord>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select plan_time as planTime from fus_after_hos_record where card_number=:cardNumber and card_type=:cardType and plan_time <=:today " +
                                " and follow_up_status in(0,1,2) and route_detail_type in (1,3,6) group by plan_time order by plan_time desc ");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setParameter("cardType", cardType);
                        query.setParameter("cardNumber", cardNumber);
                        query.setParameter("today", today);
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<FusAfterHosRecord> fusAfterPlanList = new ArrayList();
                        List<Map> list = query.list();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                fusAfterPlanList.add(ConversionUtils.convert(map, FusAfterHosRecord.class));
                            }
                            setResult(fusAfterPlanList);
                        } else {
                            setResult(null);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    //居民端查询指定随访计划时间的 随访数据
    public List<QueryPlanListResponse.FusAfterDatePlan> queryPlanList(final String cardType, final String cardNumber, final Date planTime) {
        HibernateStatelessResultAction<List<QueryPlanListResponse.FusAfterDatePlan>> action =
                new AbstractHibernateStatelessResultAction<List<QueryPlanListResponse.FusAfterDatePlan>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append("select a.record_id as recordId, a.organization_id as organizationId, a.plan_time as planTime,a.route_detail_id as routeDetailId " +
                                ",a.route_detail_type as routeDetailType,a.route_id as routeId,a.department_id as departmentId ");
                        sql.append(",a.department_name as departmentName,a.follow_up_status as followUpStatus ");
                        sql.append(" from fus_after_hos_record a where a.card_number=:cardNumber and a.card_type =:cardType and a.plan_time=:planTime and a.follow_up_status in (0,1,2) ");
                        sql.append(" and a.route_detail_type in (1,3,6) order by a.plan_time desc");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setParameter("cardType", cardType);
                        query.setParameter("cardNumber", cardNumber);
                        query.setParameter("planTime", planTime);
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<QueryPlanListResponse.FusAfterDatePlan> followUpPlanList = Lists.newArrayList();
                        List<Map> list = query.list();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                followUpPlanList.add(ConversionUtils.convert(map, QueryPlanListResponse.FusAfterDatePlan.class));
                            }
                            setResult(followUpPlanList);
                        } else {
                            setResult(null);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    public List<DiseasesAndFusListResponse> queryEducationList(final QueryPlanListRequest request) {
        HibernateStatelessResultAction<List<DiseasesAndFusListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<DiseasesAndFusListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append("select DISTINCT(b.education_id) educationId ,b.education_name educationName from fus_after_hos_record a,fus_after_health_education b where a.record_id=b.record_id  and a.route_detail_type=6 ");
                        if (org.apache.commons.lang3.StringUtils.isNotEmpty(request.getCardType())&& org.apache.commons.lang3.StringUtils.isNotEmpty(request.getCardNumber())){
                            sql.append(" and a.card_type=:cardType and a.card_number=:cardNumber ");
                        }
                        Query query = ss.createSQLQuery(sql.toString());
                        if (org.apache.commons.lang3.StringUtils.isNotEmpty(request.getCardType())&& org.apache.commons.lang3.StringUtils.isNotEmpty(request.getCardNumber())){
                            query.setParameter("cardType", request.getCardType());
                            query.setParameter("cardNumber", request.getCardNumber());
                        }
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<DiseasesAndFusListResponse> followUpPlanList = Lists.newArrayList();
                        List<Map> list = query.list();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                followUpPlanList.add(ConversionUtils.convert(map, DiseasesAndFusListResponse.class));
                            }
                            setResult(followUpPlanList);
                        } else {
                            setResult(null);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }



}
